﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace OrmSmackdown.LinqToSql
{
    public class Exerciser : IExerciser
    {
        Linq2SqlDataContext ctx;
        public Exerciser()
        {
            ctx = new Linq2SqlDataContext();
        }

        #region Insert Method
        public void Insert()
        {
            using (var ctx = new Linq2SqlDataContext())
            {
                var managerRole = new Role { RoleName = Constants.ManagerRole, IsForContact = false, IsForPortal = true };
                var employeeRole = new Role { RoleName = Constants.EmployeeRole, IsForContact = false, IsForPortal = true };
                var contactRole = new Role { RoleName = Constants.ContactRole, IsForContact = true, IsForPortal = false };

                var user1 = new User { FirstName = Constants.User1FirstName, LastName = Constants.User1LastName, Email = Constants.User1Email, MyRole = managerRole };
                var user2 = new User { FirstName = Constants.User2FirstName, LastName = Constants.User2LastName, Email = Constants.User2Email, MyRole = employeeRole };
                var user3 = new User { FirstName = Constants.User3FirstName, LastName = Constants.User3LastName, Email = Constants.User3Email, MyRole = employeeRole };
                var user4 = new User { FirstName = Constants.User4FirstName, LastName = Constants.User4LastName, Email = Constants.User4Email, MyRole = managerRole };
                var user5 = new User { FirstName = Constants.User5FirstName, LastName = Constants.User5LastName, Email = Constants.User5Email, MyRole = employeeRole };

                var company1 = new Company { CompanyName = Constants.Company1 };
                var company2 = new Company { CompanyName = Constants.Company2 };

                var contact1 = new Contact { FirstName = Constants.Contact1FirstName, LastName = Constants.Contact1LastName, Email = Constants.Contact1Email, MyRole = contactRole, Company = company1 };
                var contact2 = new Contact { FirstName = Constants.Contact2FirstName, LastName = Constants.Contact2LastName, Email = Constants.Contact2Email, MyRole = contactRole, Company = company1 };
                var contact3 = new Contact { FirstName = Constants.Contact3FirstName, LastName = Constants.Contact3LastName, Email = Constants.Contact3Email, MyRole = contactRole, Company = company2 };
                var contact4 = new Contact { FirstName = Constants.Contact4FirstName, LastName = Constants.Contact4LastName, Email = Constants.Contact4Email, MyRole = contactRole, Company = company2 };
                var contact5 = new Contact { FirstName = Constants.Contact5FirstName, LastName = Constants.Contact5LastName, Email = Constants.Contact5Email, MyRole = contactRole, Company = company2 };
                var contact6 = new Contact { FirstName = Constants.Contact6FirstName, LastName = Constants.Contact6LastName, Email = Constants.Contact6Email, MyRole = contactRole, Company = company2 };

                var portal1 = new Portal { PortalName = Constants.Portal1,  AdminUser = user1 };
                var portal2 = new Portal { PortalName = Constants.Portal2, AdminUser = user4 };

                ctx.Roles.InsertOnSubmit(managerRole);
                ctx.Roles.InsertOnSubmit(employeeRole);
                ctx.Roles.InsertOnSubmit(contactRole);

                ctx.Companies.InsertOnSubmit(company1);
                ctx.Companies.InsertOnSubmit(company2);

                ctx.Persons.InsertOnSubmit(user1);
                ctx.Persons.InsertOnSubmit(user2);
                ctx.Persons.InsertOnSubmit(user3);
                ctx.Persons.InsertOnSubmit(user4);
                ctx.Persons.InsertOnSubmit(user5);

                ctx.Persons.InsertOnSubmit(contact1);
                ctx.Persons.InsertOnSubmit(contact2);
                ctx.Persons.InsertOnSubmit(contact3);
                ctx.Persons.InsertOnSubmit(contact4);
                ctx.Persons.InsertOnSubmit(contact5);
                ctx.Persons.InsertOnSubmit(contact6);

                ctx.Portals.InsertOnSubmit(portal1);
                ctx.Portals.InsertOnSubmit(portal2);

                AddAddress(ctx, user1, "1000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, user2, "2000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, user3, "3000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, user4, "4000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, user5, "5000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, user1, "1100 Some St.", string.Empty, Constants.TownWeCareAbout, "ST", "12345", "1234", true);
                AddAddress(ctx, user2, "1200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, user3, "1300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, user4, "1400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, user5, "1500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);

                AddAddress(ctx, contact1, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact2, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact3, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact4, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact5, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact6, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(ctx, contact1, "8000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, contact2, "8100 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, contact3, "8200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, contact4, "8300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, contact5, "8400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(ctx, contact6, "8500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            
                var ppxr11 = new PortalPeopleXref() { Portal = portal1, User = user1 };

                var ppxr21 = new PortalPeopleXref() { Portal = portal1, User = user2 };
                var ppxr22 = new PortalPeopleXref() { Portal = portal2, User = user2 };

                var ppxr31 = new PortalPeopleXref() { Portal = portal1, User = user3 };
                var ppxr32 = new PortalPeopleXref() { Portal = portal2, User = user3 };

                var ppxr42 = new PortalPeopleXref() { Portal = portal2, User = user4 };

                var ppxr52 = new PortalPeopleXref() { Portal = portal2, User = user5 };

                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr11);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr21);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr22);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr31);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr32);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr42);
                ctx.PortalPeopleXrefs.InsertOnSubmit(ppxr52);

                ctx.SubmitChanges();
            }
        }

        private void AddAddress(Linq2SqlDataContext ctx, Person p, string line1, string line2, string city, string state, string zip, string zip4, bool isDefault)
        {
            var addr = new Address { Line1 = line1, Line2 = line2, City = city, State = state, ZipCode = zip, ZipCodePlusFour = zip4, IsDefault = isDefault, Person = p };
            ctx.Addresses.InsertOnSubmit(addr);
        }
        #endregion


        public IList<string> GetUserEmails()
        {
            return (from user in ctx.Persons.OfType<User>() select user.Email).ToList(); 
        }

        public IList<string> GetContactEmails()
        {
            return (from user in ctx.Persons.OfType<Contact>() select user.Email).ToList();
        }

        public IList<string> GetPortalAdminEmails()
        {
            return (from portals in ctx.Portals select portals.AdminUser.Email).ToList();
        }

        public IList<string> GetContactEmailsForCompany(string companyName)
        {
            return ctx.Companies.Where(cmp => cmp.CompanyName == companyName).FirstOrDefault().Contacts.Select(fld => fld.Email).ToList();
        }

        public int GetNumberOfPortals()
        {
            return ctx.Portals.Count();
        }

        public int GetNumberOfUsers()
        {
            return ctx.Persons.OfType<User>().Count();
        }

        public int GetNumberOfContacts()
        {
            return ctx.Persons.OfType<Contact>().Count();
        }

        public string GetDefaultCity(string email)
        {
            var city = from address in ctx.Addresses
                       join usr in ctx.Persons.OfType<User>() on address.Person.PersonId equals usr.PersonId
                       where address.IsDefault == true && usr.Email == email
                       select address.City;

            return city.FirstOrDefault();

        }

        public string GetAdminForPortal(string portalName)
        {
            var email = from prsn in ctx.Persons.OfType<User>()
                        join prt in ctx.Portals on prsn equals prt.AdminUser
                        where prt.PortalName == portalName
                        select prsn.Email;

            return email.FirstOrDefault();
        }

        public double GetAverageUsersPerPortal()
        {
            var portals = ctx.Portals.ToList();

            ///Ehhh... on this one, fairly certain there is a better way, runs three
            ///queries, yuk!
            int numbUsers = 0;
            foreach (var portal in portals)
                numbUsers += portal.PortalPeopleXrefs.Count;

            return (double)numbUsers / (double)portals.Count;
        }


        public void Dispose()
        {
            ctx.Dispose();
        }

    }
}
